<?php
/**
 * RedCross Application
 *
 * @copyright Copyright (c) 2010, Kristian Stokke Tryggestad, Rune Vikestad, Andreas Flaten Wist, Simen Nicolai Echholt, Ole Henrik Jahren
 */

/**
 * Member gateway class for communicating with persistent storage
 */
class Core_Gateway_Course extends RedCross_Gateway_GatewayAbstract
{
    /**
     * Constructor
     * Defines some private variables
     */
    public function __construct()
    {
        $this->_class = 'Course';
        $this->_modelPath = 'Core_Model';
    }

    public function fetchAllCourses()
    {
        $query = Doctrine_Query::create()
            ->select('*, count(m.id) as num_members')
            ->from($this->getModelClass() . ' c')
            ->leftJoin('c.Members as m')
            ->groupBy('c.id')
            ->where('c.type = ?', 'course');

        return $query->execute();
    }

    public function fetchAllCompetences()
    {
        $query = Doctrine_Query::create()
            ->select('*, count(m.id) as num_members')
            ->from($this->getModelClass() . ' c')
            ->leftJoin('c.Members as m')
            ->groupBy('c.id')
            ->where('c.type = ?', 'competence');

        return $query->execute();
    }

    public function fetchAllNonArchived()
    {
        $query = Doctrine_Query::create()
            ->select()
            ->from($this->getModelClass() . ' c')
            ->where('c.archived_date IS NULL');

        return $query->execute();
    }

    public function fetchAllArchived()
    {
        $query = Doctrine_Query::create()
            ->select()
            ->from($this->getModelClass() . ' c')
            ->where('c.archived_date IS NOT NULL');

        return $query->execute();
    }

    public function fetchAllForMember($memberId)
    {
        $query = Doctrine_Query::create()
            ->select()
            ->from('Core_Model_Course c')
            ->leftJoin('c.MemberCourse mc')
            ->where('mc.member_id = ?', $memberId)
            ->fetchArray();

        return $query;
    }

    public function fetchAllCoursesForMember($memberId)
    {
        $query = Doctrine_Query::create()
            ->select()
            ->from('Core_Model_Course c')
            ->leftJoin('c.MemberCourse mc')
            ->where('c.type = ?', 'course')
            ->andWhere('mc.member_id = ?', $memberId)
            ->orderBy('mc.expires_date ASC') 
            ->fetchArray();

        return $query;
    }

    public function fetchAllExpiringCoursesForMember($memberId) {
        // TODO: Ick, find a better way to do this. There must be some
        // native MySQL function for this?
        //
        // 1 month = 2 629 743.83 seconds

        $query = Doctrine_Query::create()
            ->select()
            ->from('Core_Model_Course c')
            ->leftJoin('c.MemberCourse mc')
            ->where('c.type = ?', 'course')
            ->andWhere('mc.member_id = ?', $memberId)
            ->andWhere('mc.expires_date IS NOT NULL')
            ->andWhere('unix_timestamp(mc.expires_date)-unix_timestamp(now()) < 2629743')
            ->andWhere('unix_timestamp(mc.expires_date)-unix_timestamp(now()) > 0');

        return $query->fetchArray();
    }

    public function fetchMembersWithAccess($courseId) {
        $query = Doctrine_Query::create()
            ->select()
            ->from('Core_Model_Member m')
            ->leftJoin('m.MemberCourse mc')
            ->where('ma.course_id = ?', $courseId)
            ->andWhere('ma.lost_access_date IS NOT NULL')
            ->andWhere('unix_timestamp(mc.expires_date)-unix_timestamp(now()) > 0');
        return $query->execute();
    }

    public function fetchAllCompetencesForMember($memberId)
    {
        $query = Doctrine_Query::create()
            ->select()
            ->from('Core_Model_Course c')
            ->leftJoin('c.MemberCourse mc')
            ->where('c.type = ?', 'competence')
            ->andWhere('mc.member_id = ?', $memberId)
            ->orderBy('mc.expires_date ASC') 
            ->fetchArray();

        return $query;
    }
}
